package com.xishu.dao.jpa;

import com.xishu.entity.order.OrderTypeMapping;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface OrderTypeMappingDao extends JpaRepository<OrderTypeMapping, Long>, JpaSpecificationExecutor<OrderTypeMapping> {
    /**
     * 年月日
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  , order_type as tmpId, sum(cash) as value " +
            "from order_type_mapping m" +
            " where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.flag_delete = false " +
            "group by m.order_type,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> report(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    @Query("select  orderType as tmpId, sum(cash) as value , count(*) as count from order_type_mapping m where m.shopId =?1 and m.createTime >= ?2 and m.createTime <= ?3 group by m.orderType")
    public List<Tuple> count(Long shopId, Long startTime, Long endTime);

    /**
     * 总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select order_type as tmpId, sum(cash) as value " +
            "from order_type_mapping m" +
            " where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.flag_delete = false " +
            "group by m.order_type", nativeQuery = true)
    List<Tuple> reportTotal(List<Long> shopIdList, Long startTime, Long endTime);


}
